package com.qf.dao.impl;

import com.qf.dao.UserDao;
import com.qf.pojo.User;
import com.qf.utils.DruidJbdcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @author Kuke
 * @date 2021/11/18 14:32
 * 针对用户访问的数据接口实现
 */
public class UserDaoImpl implements UserDao {
    /**
     * 数据接口查询所有的用户信息,封装List集合
     * @return 返回list列表
     */
    @Override
    public List<User> selectAllUser() throws SQLException {
        //创建执行对象
        QueryRunner qr = new QueryRunner(DruidJbdcUtils.getDataSource()) ;
        //sql
        String sql = "select * from user" ;
        //执行
        List<User> list = qr.query(sql, new BeanListHandler<User>(User.class));
        return list;
    }

    /**
     * 数据接口访问查询用户通过用户id
     * @param uid  用户id编号
     * @return  用户实体
     */
    @Override
    public User selectUserById(String uid) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidJbdcUtils.getDataSource()) ;
        String sql = "select  * from user where uid = ?" ;
        User user = qr.query(sql, new BeanHandler<User>(User.class), uid);
        return user;
    }

    /**
     * 更新用户数据
     * @param user 用户实体
     * @return 影响行数
     */
    @Override
    public int update(User user) throws SQLException {
        //创建执行对象
        QueryRunner qr = new QueryRunner(DruidJbdcUtils.getDataSource()) ;
        String sql = "update user set username = ? , password= ?, name = ?,telephone=?,birthday=?,sex=? where uid = ?" ;
        int count = qr.update(sql, user.getUsername(), user.getPassword(),
                user.getName(),
                user.getTelephone(),
                user.getBirthday(),
                user.getSex(),
                user.getUid());

        return count;
    }

    /**
     * 用户数据接口删除用户通过uid
     * @param uid 用户uid
     * @return 影响行数
     */
    @Override
    public int deleteUserByUid(String uid) throws SQLException {

        //创建执行对象
        QueryRunner qr =  new QueryRunner(DruidJbdcUtils.getDataSource()) ;
        //sql
        String sql = "delete from user where uid = ?" ;
        int count = qr.update(sql, uid);
        System.out.println(count);
        return count ;


    }

    /**
     * 通过分页查询用户列表数据
     * @param currentPage 当前页码
     * @param pageSize  每页显示条数
     * @return  返回用户列表数据
     */
    @Override
    public List<User> selectUserByPage(int currentPage, int pageSize) throws SQLException {

        //执行对象
        QueryRunner qr = new QueryRunner(DruidJbdcUtils.getDataSource()) ;
        //sql
        String sql = "select * from user limit ?,?" ; //起始行数,每页显示条数
        //执行查询
        List<User> list = qr.query(sql, new BeanListHandler<User>(User.class),
                (currentPage - 1) * pageSize,
                pageSize);
        return list;
    }
    /**
     * 查询用户的总记录数
     * @return 返回总计数
     */
    @Override
    public int getTotalCount() throws SQLException {
        //执行
        QueryRunner qr = new QueryRunner(DruidJbdcUtils.getDataSource()) ;
        //sql
        String sql = "select count(uid) from user" ;
        Object obj = qr.query(sql, new ScalarHandler<>());
        String str = String.valueOf(obj);
        int totalCount = Integer.parseInt(str);
        return totalCount;
    }

    /**
     * 用户的数据接口来通过用户查询用户
     * @param username  指定的用户名
     * @return 返回指定的用户实体
     */
    @Override
    public User selectUserByUsername(String username) throws SQLException {
        //创建执行对象
        QueryRunner qr = new QueryRunner(DruidJbdcUtils.getDataSource()) ;
        //sql
        String sql = "select * from user where username = ?" ;
        //执行
        User user = qr.query(sql, new BeanHandler<User>(User.class), username);
        return user;
    }

    /**
     * 用户数据接口添加用户信息
     * @param user  用户实体类
     * @return 影响的行数
     */
    @Override
    public int addUser(User user) throws SQLException {
        //创建执行对象
        QueryRunner qr = new QueryRunner(DruidJbdcUtils.getDataSource()) ;
        //sql
        String sql = "insert into user values(?,?,?,?,?,?,?,?,?,?)" ;
        //更新
        int count = qr.update(sql,
                user.getUid(),
                user.getUsername(),
                user.getPassword(),
                user.getName(),
                user.getEmail(),
                user.getTelephone(),
                user.getBirthday(),
                user.getSex(),
                user.getState(),
                user.getCode());
        return count;
    }

    /**
     * 用户数据接口:通过激活码 找用户
     * @param code 用户激活码
     * @return  返回用户实体
     */
    @Override
    public User selectUserByCode(String code) throws SQLException {
        //执行对象
        QueryRunner qr = new QueryRunner(DruidJbdcUtils.getDataSource()) ;
        //sql
        String sql = "select * from user where code = ?" ;
        User user = qr.query(sql, new BeanHandler<User>(User.class), code);
        return user;
    }


    /**
     * 将用户的状态更新
     * @param user
     */
    @Override
    public void updateUser(User user) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidJbdcUtils.getDataSource()) ;
            //sql
        String sql = "update user set state = ? where uid = ? " ;
        int count = qr.update(sql, user.getState(), user.getUid());
        System.out.println(count);
    }

    /**
     * 用户数据接口:通过用户名和密码查询用户
     * @param username
     * @param password
     * @return
     */
    @Override
    public User selectUser(String username, String password) throws SQLException {
        //创建执行对象
        QueryRunner qr = new QueryRunner(DruidJbdcUtils.getDataSource()) ;
        //sql
        String sql = "select * from user where username = ? and password  = ?" ;
        User user = qr.query(sql, new BeanHandler<User>(User.class), username, password);
        return user;
    }


}
